﻿using System.Net;
using System.Windows.Forms;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using System.IO;
using System.Data;
using SDD;
using NPOI.HSSF.UserModel;
using System;
using SDD.Properties;
using System.Collections;
using System.Collections.Generic;

namespace SDD
{
    class BoardMeetingData
    {
        //召開股東常(臨時)會日期、地點資料彙總表
        //url 須另外帶年度資料
        public string url1 = "http://mopsov.tse.com.tw/server-java/t108sb31?colorchg=1&step=1&TYPEK=sii&YEAR={0}&";

        private const string FileText = "召開股東常(臨時)會日期";
        private string FullFileName = Program.FileDir + FileText + ".xls";
        public string BMYear = Settings.Default.BoardMeetingYear;
        public string dataDay = Program.DataDay;
        public string msg = "";
        public dds.ddtDataTable dt1 = new dds.ddtDataTable();
        public dsROE.ROEDataTable dt_roe = new dsROE.ROEDataTable();

        public void AggregateData()
        {
            try
            {
                if (BMYear == "" || BMYear == "0")
                    return;
                Get5YearROE();
                analysisHTML(url1, dt1);
                //GetOwningStocksRate("3264");
                //GetOwningStocksRate("2527");

                // InsertData();
            }
            catch (Exception ex)
            {
                Console.WriteLine(" 資料擷取失敗!! " + ex.Message);
            }

        }
        /// <summary>
        /// 解析HTML new 董監改選名單
        /// </summary>
        /// <param name="url"></param>
        /// <param name="ds"></param>
        public void analysisHTML(string url, dds.ddtDataTable dt)
        {
            url = string.Format(url, BMYear);
            string context = new cHttp().dopost(url);
            WebBrowser wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);

            HtmlElementCollection tb = wb.Document.GetElementsByTagName("TABLE");

            HtmlElementCollection tr = tb[0].GetElementsByTagName("TR");
            int count = 0;
            int datacnt = 1;
            Hashtable ht = new Hashtable();
            /////////////////////
            //建立工作表
            NPOIComFunction xls = new NPOIComFunction(FullFileName);
            if (xls.Workbook.GetSheetIndex(dataDay) > -1)
            {
                Console.WriteLine("召開股東常(臨時)會日期:  " + dataDay + " 資料已存在!!!");
                return;
            }
            HSSFSheet sheet = xls.Workbook.CreateSheet(dataDay);
            CreateDataHeader(sheet);

            // 資料迴圈
            foreach (HtmlElement trElement in tr)
            {
                HtmlElementCollection td = trElement.GetElementsByTagName("TD");
                if (count >= 1 && !ht.ContainsKey(td[0].InnerText)) //跳過表頭
                {
                    WriteData(ref sheet, ref td, ref datacnt);
                    ht.Add(td[0].InnerText, td[1].InnerText);
                }
                count++;
            }

            //往前推3年
            count = 0;
            int yy = 0;
            int.TryParse(BMYear, out yy);
            url = string.Format(url1, (yy - 3));
            context = new cHttp().dopost(url);
            wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);
            tb = wb.Document.GetElementsByTagName("TABLE");

            tr = tb[0].GetElementsByTagName("TR");
            foreach (HtmlElement trElement in tr)
            {
                HtmlElementCollection td = trElement.GetElementsByTagName("TD");
                if (count >= 1 && !ht.ContainsKey(td[0].InnerText)) //跳過表頭
                {
                    WriteData(ref sheet, ref td, ref datacnt);
                    ht.Add(td[0].InnerText, td[1].InnerText);
                }
                count++;
            }
            //讓目前的工作表選為預設
            xls.Workbook.ActiveSheetIndex = xls.Workbook.NumberOfSheets - 1;
            xls.Workbook.SetSelectedTab(xls.Workbook.NumberOfSheets - 1);
            sheet.IsSelected = true;

            xls.WriteToFile();
            wb.Dispose();
        }

        /// <summary>
        /// 建立資料表頭
        /// </summary>
        /// <param name="sheet"></param>
        private void CreateDataHeader(HSSFSheet sheet)
        {
            sheet.CreateRow(0).CreateCell(0).SetCellValue("股票代號");
            sheet.CreateRow(0).CreateCell(1).SetCellValue("股票名稱");
            sheet.CreateRow(0).CreateCell(2).SetCellValue("股東會日期");
            sheet.CreateRow(0).CreateCell(3).SetCellValue("股東會");
            sheet.CreateRow(0).CreateCell(4).SetCellValue("改選");

            sheet.CreateRow(0).CreateCell(5).SetCellValue("比例");
            sheet.CreateRow(0).CreateCell(6).SetCellValue("張數");
            sheet.CreateRow(0).CreateCell(7).SetCellValue("佔股本比例");

            // 
            sheet.CreateRow(0).CreateCell(8).SetCellValue("收盤價");
            sheet.CreateRow(0).CreateCell(9).SetCellValue("本益比");
            sheet.CreateRow(0).CreateCell(10).SetCellValue("總市值(百萬)");
            sheet.CreateRow(0).CreateCell(11).SetCellValue("每股淨值(元)");
            sheet.CreateRow(0).CreateCell(12).SetCellValue("每股營收(元)");
            sheet.CreateRow(0).CreateCell(13).SetCellValue("負債比例");
            sheet.CreateRow(0).CreateCell(14).SetCellValue("股價淨值比");
            sheet.CreateRow(0).CreateCell(15).SetCellValue("股本(億)");
            sheet.CreateRow(0).CreateCell(16).SetCellValue("營業毛利率");
            sheet.CreateRow(0).CreateCell(17).SetCellValue("現金股利(元)");
            sheet.CreateRow(0).CreateCell(18).SetCellValue("營業利益率");
            sheet.CreateRow(0).CreateCell(19).SetCellValue("股票股利");
            sheet.CreateRow(0).CreateCell(20).SetCellValue("稅前淨利率");
            sheet.CreateRow(0).CreateCell(21).SetCellValue("資產報酬率");
            sheet.CreateRow(0).CreateCell(22).SetCellValue("前1年ROE");
            sheet.CreateRow(0).CreateCell(23).SetCellValue("前2年ROE");
            sheet.CreateRow(0).CreateCell(24).SetCellValue("前3年ROE");
            sheet.CreateRow(0).CreateCell(25).SetCellValue("前4年ROE");
            sheet.CreateRow(0).CreateCell(26).SetCellValue("前5年ROE");

        }

        /// <summary>
        /// 抓董監持股比數
        /// </summary>
        /// <param name="sid"></param>
        /// <returns></returns>
        public string GetOwningStocksRate(string sid)
        {
            double persent = 0;
            string url = string.Format("http://jsjustweb.jihsun.com.tw/z/zc/zck/zck_{0}.asp.htm", sid);
            string context = new cHttp().dopost(url);
            WebBrowser wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);
            wb.Document.Write(context);
            HtmlElementCollection tb = wb.Document.GetElementsByTagName("TABLE");

            HtmlElementCollection tr = null;
            try
            {
                tr = tb[6].GetElementsByTagName("TR");
            }
            catch //無申讓 table的數量不一樣 會引發錯誤;
            {
                try
                {
                    tr = tb[5].GetElementsByTagName("TR");
                }
                catch // 查無資料
                {
                    return "0";
                }
            }

            int count = 0;
            List<string> data = new List<string>();
            foreach (HtmlElement trElement in tr)
            {
                HtmlElementCollection td = trElement.GetElementsByTagName("TD");
                if (count >= 3 && count < tr.Count - 1) //跳過表頭和最後一列資料
                {
                    // 職稱和持股張數 仍會有超過100%的可能 td[0].InnerText + td[3].InnerText
                    // 改成持股張數和持股比率 td[3].InnerText + td[4].InnerText
                    if (data.Contains(td[3].InnerText + td[4].InnerText) == false)
                    {
                        data.Add(td[3].InnerText + td[4].InnerText);
                        double temp_per = 0;
                        double.TryParse(td[3].InnerText.Replace("%", "").ToString(), out temp_per);
                        persent += temp_per;
                    }
                }
                count++;
            }
            wb.Dispose();
            return persent.ToString();
        }

        /// <summary>
        /// 抓董監持股比數2
        /// </summary>
        /// <param name="sid"></param>
        /// <returns></returns>
        public string[] GetOwningStocksRate2(string sid)
        {
            string[] result = new string[2];
            string url = string.Format("http://jsjustweb.jihsun.com.tw/z/zc/zcx/zcxD1.djjs?A={0}", sid);

            string context = new cHttp().dopost(url);
            WebBrowser wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);
            wb.Document.Write(context);
            HtmlElementCollection tb = wb.Document.GetElementsByTagName("TABLE");

            HtmlElementCollection tr = null;
            try
            {
                tr = tb[1].GetElementsByTagName("TR");
            }
            catch
            {
            }
            HtmlElementCollection td = null;
            try
            {
                td = tr[2].GetElementsByTagName("TD");
                result[0] = td[1].InnerText;
                result[1] = td[2].InnerText;
            }
            catch
            {
            }
            wb.Dispose();
            return result;
        }

        /// <summary>
        /// 抓股票基本資料
        /// </summary>
        /// <param name="sid"></param>
        /// <returns></returns>
        public List<string> GetStocksBasickData(string sid)
        {
            List<string> result = new List<string>();
            string url = string.Format("http://jsjustweb.jihsun.com.tw/z/zc/zca/zca_{0}.asp.htm", sid);

            string context = new cHttp().dopost(url);
            WebBrowser wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);
            wb.Document.Write(context);
            HtmlElementCollection tb = wb.Document.GetElementsByTagName("TABLE");

            HtmlElementCollection tr = null;
            try
            {
                tr = tb[2].GetElementsByTagName("TR");
            }
            catch
            {
            }
            result.Add(tr[1].GetElementsByTagName("TD")[7].InnerText);
            result.Add(tr[3].GetElementsByTagName("TD")[1].InnerText);
            result.Add(tr[5].GetElementsByTagName("TD")[1].InnerText);
            result.Add(tr[7].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[9].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[10].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[11].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[14].GetElementsByTagName("TD")[1].InnerText);
            result.Add(tr[14].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[14].GetElementsByTagName("TD")[5].InnerText);
            result.Add(tr[15].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[15].GetElementsByTagName("TD")[5].InnerText);
            result.Add(tr[16].GetElementsByTagName("TD")[3].InnerText);
            result.Add(tr[17].GetElementsByTagName("TD")[3].InnerText);

            //HtmlElementCollection td = null;

            wb.Dispose();
            return result;
        }

        /// <summary>
        /// 取得五年ROE
        /// </summary>
        public void Get5YearROE()
        {
            //http://www.twstock.org/roesii_json.php
            string url = "http://www.twstock.org/roesii_json.php";
            string context = new cHttp().dopost(url);
            WebBrowser wb = new WebBrowser();
            wb.Navigate("");

            wb.ScriptErrorsSuppressed = true;
            wb.Document.Write("<script type='text/javascript'>window.onerror = function(){};</script>" + context);
            wb.Document.Write(context);
            System.IO.StreamReader getReader = new System.IO.StreamReader(
                                            wb.DocumentStream,
                                            System.Text.Encoding.Unicode);
            while (getReader.Peek() > 0)
            {
                string data = getReader.ReadLine();
                Match m_ID = Regex.Match(data, @".*[cid=](?<id>\d{4,})\s*.*[</a>]\W{3}(?<roe1>[-]?\d*[.]?\d*)\W{3}(?<roe2>[-]?\d*[.]?\d*)\W{3}(?<roe3>[-]?\d*[.]?\d*)\W{3}(?<roe4>[-]?\d*[.]?\d*)\W{3}(?<roe5>[-]?\d*[.]?\d*).*");
                if (m_ID.Success)
                {
                    dsROE.ROERow dr = dt_roe.NewROERow();
                    dr.ID = m_ID.Groups["id"].Value;
                    dr.ROE1 = m_ID.Groups["roe1"].Value;
                    dr.ROE2 = m_ID.Groups["roe2"].Value;
                    dr.ROE3 = m_ID.Groups["roe3"].Value;
                    dr.ROE4 = m_ID.Groups["roe4"].Value;
                    dr.ROE5 = m_ID.Groups["roe5"].Value;
                    dt_roe.Rows.Add(dr);
                }
                //    else
                //{
                //    Debug.WriteLine(data);
                //}
            }
            wb.Dispose();
        }

        /// <summary>
        /// 寫入資料
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="td"></param>
        /// <param name="datacnt"></param>
        public void WriteData(ref HSSFSheet sheet,
                                ref HtmlElementCollection td,
                                ref int datacnt)
        {
            if (td[6].InnerText.Equals("是"))
            {
                // 寫入資料
                // 寫股東會資料
                Console.WriteLine(td[0].InnerText + ";" + td[1].InnerText);
                sheet.CreateRow(datacnt).CreateCell(0).SetCellValue(td[0].InnerText);
                sheet.CreateRow(datacnt).CreateCell(1).SetCellValue(td[1].InnerText);
                sheet.CreateRow(datacnt).CreateCell(2).SetCellValue(td[3].InnerText);
                sheet.CreateRow(datacnt).CreateCell(3).SetCellValue(td[4].InnerText);
                sheet.CreateRow(datacnt).CreateCell(4).SetCellValue(td[6].InnerText);

                sheet.CreateRow(datacnt).CreateCell(5).SetCellValue(GetOwningStocksRate(td[0].InnerText));
                sheet.CreateRow(datacnt).CreateCell(6).SetCellValue(GetOwningStocksRate2(td[0].InnerText)[0]);
                sheet.CreateRow(datacnt).CreateCell(7).SetCellValue(GetOwningStocksRate2(td[0].InnerText)[1]);
                try
                {
                    // 寫基本資料
                    int i = 8;
                    foreach (string data in GetStocksBasickData(td[0].InnerText))
                    {
                        sheet.CreateRow(datacnt).CreateCell(i).SetCellValue(data);
                        i++;
                    }

                    // 寫ROE資料
                    dsROE.ROERow dr_roe = dt_roe.Select("ID='" + td[0].InnerText + "'")[0] as dsROE.ROERow;
                    sheet.CreateRow(datacnt).CreateCell(i++).SetCellValue(dr_roe.ROE1);
                    sheet.CreateRow(datacnt).CreateCell(i++).SetCellValue(dr_roe.ROE2);
                    sheet.CreateRow(datacnt).CreateCell(i++).SetCellValue(dr_roe.ROE3);
                    sheet.CreateRow(datacnt).CreateCell(i++).SetCellValue(dr_roe.ROE4);
                    sheet.CreateRow(datacnt).CreateCell(i++).SetCellValue(dr_roe.ROE5);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                datacnt++;
            }
        }

    }
}
